﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FYPDemo
{
    public class Common
    {
        public static DataTable bindStatus()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listStatus";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        public static DataTable bindState()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "listState";
                cmd.CommandType = CommandType.StoredProcedure;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable searchOrderID(string OrderID)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "showOrderDetails";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@orderID", SqlDbType.VarChar, 20));
                cmd.Parameters["@orderID"].Value = OrderID;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable searchVendor(string keyword)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "searchByVendor";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Keyword", SqlDbType.VarChar, 50));
                cmd.Parameters["@Keyword"].Value = keyword;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static DataTable searchProduct(string keyword)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "searchByProduct";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Keyword", SqlDbType.VarChar, 50));
                cmd.Parameters["@Keyword"].Value = keyword;

                da.SelectCommand = cmd;
                da.Fill(dt);
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return dt;
        }

        public static string getVendorByProductID(string productID)
        {
            string vendor = "";

            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();

            conndb db = new conndb();
            SqlConnection con = db.GetOpenConnection();
            SqlCommand cmd = con.CreateCommand();

            try
            {
                cmd.CommandText = "getVendorByProductID";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 30));
                cmd.Parameters["@ProductID"].Value = productID;

                da.SelectCommand = cmd;
                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    vendor = dt.Rows[0][0].ToString();
                }
            }

            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }

            finally
            {
                con.Close();
            }

            return vendor;
        }
    }
}